﻿
using MySql.Data.MySqlClient;
using newBlog.CenterInc.model;
using newBlog.inc.SqlINC;
using System;
using System.Collections.Generic;

namespace newBlog.CenterInc.Ctrltor
{
    public class Article_inc
    {
        sqlConnect Conn = new sqlConnect();
        public int Article_count(string search)
        {
            string sql = "select count(Blog_article_id) from blog_article where Blog_article_title like '%" + search + "%' and Blog_article_state=1";

            return Conn.DataValue(sql);
        }//文章的数额
        public List<Article_itemDao> article_items(int page, string search)
        {
            List<Article_itemDao> arr = new List<Article_itemDao>();
            string sql = "SELECT " +
                "blog_article.Blog_article_id," +
                "Blog_article_title," +
                "Blog_user_portrait," +
                "Blog_user_name," +
                "Blog_article_price," +
                "Blog_article_Date," +
                "Blog_article_img," +
                "Blog_catalog_name," +
                "Blog_article_describe," +
                "count(Blog_comment_id) as commentCount " +
                "FROM " +
                "blog_article " +
                "LEFT JOIN blog_user ON blog_article.Blog_user_id = blog_user.Blog_user_id " +
                "LEFT JOIN blog_catalog ON blog_article.Blog_catalog_id = blog_catalog.Blog_catalog_id " +
                "LEFT JOIN blog_comment ON blog_article.Blog_article_id=blog_comment.Blog_article_id " +
                "where Blog_article_title like '%{0}%' and Blog_article_state=1 " +
                "GROUP BY blog_article.Blog_article_id " +
                "ORDER BY Blog_article_Date desc " +
                "limit {1},20";

            MySqlDataReader dr = Conn.DataListAll(string.Format(sql, search, page));
            while (dr.Read())
            {
                Article_itemDao article_ItemDao = new Article_itemDao(dr.GetInt32(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetInt32(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetInt32(9));
                arr.Add(article_ItemDao);
            }
            return arr;
        }//获取通过页数获取文章信息

        public List<Article_itemDao> article_content(int id)
        {
            List<Article_itemDao> arr = new List<Article_itemDao>();
            string sql = "SELECT " +
                "blog_article.Blog_article_id," +
                "Blog_article_title," +
                "Blog_article_price," +
                "Blog_article_Date," +
                "Blog_article_img," +
                "Blog_catalog_name," +
                "Blog_article_describe," +
                "count(Blog_comment_id) AS commentCount," +
                "Blog_user_portrait," +
                "Blog_user_name," +
                "Blog_user_level," +
                "(select count(Blog_article_id) from blog_article where Blog_user_id=blog_user.Blog_user_id) as autorCount," +
                "(select count(Blog_fans_follow) from blog_fans where Blog_user_id=blog_user.Blog_user_id) as fllowCount," +
                "(SELECT count(Blog_user_id) from blog_fans where Blog_fans_follow=blog_user.Blog_user_id) as fansCount," +
                "(select Blog_grade_name from blog_grade where Blog_grade_id=Blog_user_Grade) as grade," +
                "Blog_article_content," +
                "blog_article.Blog_user_id " +
                "FROM " +
                "blog_article " +
                "LEFT JOIN blog_catalog ON blog_article.Blog_catalog_id = blog_catalog.Blog_catalog_id " +
                "LEFT JOIN blog_comment ON blog_article.Blog_article_id = blog_comment.Blog_article_id " +
                "LEFT JOIN blog_user ON blog_article.Blog_user_id = blog_user.Blog_user_id " +
                "where " +
                "blog_article.Blog_article_id = '{0}' " +
                "GROUP BY blog_article.Blog_article_id";
            object[] objarr = Conn.DataRowOne(string.Format(sql, id));
            if (objarr == null)
            {
                return null;
            }

            Article_itemDao article_ItemDao = new Article_itemDao(Convert.ToInt32(objarr[0]), objarr[1].ToString(), Convert.ToInt32(objarr[2]), objarr[3].ToString(), objarr[4].ToString(), objarr[5].ToString(), objarr[6].ToString(), Convert.ToInt32(objarr[7]), objarr[8].ToString(), objarr[9].ToString(), Convert.ToInt32(objarr[10]), Convert.ToInt32(objarr[11]), Convert.ToInt32(objarr[12]), Convert.ToInt32(objarr[13]), objarr[14].ToString(), objarr[15].ToString(),objarr[16].ToString());
            arr.Add(article_ItemDao);
            return arr;
        }//获取指定id的文章信息

        //获取指定id是否存在收费内容
        public bool GetCharge(int id)
        {
            string sql = "select not ISNULL(Blog_article_charge) from blog_article where Blog_article_id='{0}' and Blog_article_charge!=''";
            if (Conn.DataValue(string.Format(sql, id)) > 0)
            {
                return true;
            }
            return false;
        }
        public string isPay(int id, string username)
        {
            string sql = "select COUNT(*) from blog_order where Blog_order_channel='文章id_{0}' and Blog_user_id='{1}' ";
            if (Conn.DataValue(string.Format(sql, id, username)) > 0)
            {
                sql = "select Blog_article_charge from blog_article where Blog_article_id='{0}'";
                return Conn.DataOne(string.Format(sql, id));
            }
            return null;
        }//是否已经购买了文章如果购买了则返回内容

        public int getPrice(string article)
        {
            string sql = "select Blog_article_price from blog_article where Blog_article_id='{0}'";
            return Conn.DataValue(string.Format(sql, article));
        }//获取文章价格

        public List<MyArticle_itemDao_json> myArticle_ItemDao_Jsons(string user_id, int page, string minPrice, string maxPrice, string minTime, string maxTime, string catalog, string title)
        {
            List<MyArticle_itemDao_json> list = new List<MyArticle_itemDao_json>();
            string sql = "select " +
                "Blog_article_id ," +
                "Blog_article_title," +
                "Blog_article_price," +
                "Blog_article_img," +
                "Blog_catalog_name," +
                "Blog_article_describe," +
                "Blog_article_Date ," +
                "Blog_user_id " +
                "from " +
                "blog_article " +
                "LEFT JOIN blog_catalog as catalog ON blog_article.Blog_catalog_id = catalog.Blog_catalog_id " +
                "where Blog_user_id like '{0}' AND Blog_article_state = 1 {1} " +
                "order by Blog_article_Date desc " +
                "limit {2},20";
            string sql1 = "";
            if (minPrice.Length != 0)
            {
                sql1 += " and Blog_article_price>='" + minPrice + "'";
            }
            if (maxPrice.Length != 0)
            {
                sql1 += " and Blog_article_price<='" + maxPrice + "'";
            }
            if (minTime.Length != 0)
            {
                sql1 += " and Blog_article_Date>='" + minTime + "'";
            }
            if (maxTime.Length != 0)
            {
                sql1 += " and Blog_article_Date<='" + maxTime + "'";
            }
            if (catalog.Length != 0)
            {
                sql1 += " and blog_article.Blog_catalog_id='" + catalog + "'";
            }
            if (title.Length != 0) {
                sql1 += " and Blog_article_title like '%" + title + "%' ";
            }
            MySqlDataReader dr = Conn.DataListAll(string.Format(sql, user_id, sql1, page));
            while (dr.Read())
            {
                MyArticle_itemDao_json myArticle_ItemDao_Json = new MyArticle_itemDao_json(dr.GetInt32(0), dr.GetString(1), dr.GetInt32(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetString(7));
                list.Add(myArticle_ItemDao_Json);
            }
            return list;

        }//转换为json
        public int MyarticleCount(string user_id, string minPrice, string maxPrice, string minTime, string maxTime, string catalog, string title)
        {
            string sql = "select count(Blog_article_id) from blog_article where Blog_user_id like '{0}' and Blog_article_state=1 ";
            if (minPrice.Length != 0)
            {
                sql += " and Blog_article_price>='" + minPrice + "'";
            }
            if (maxPrice.Length != 0)
            {
                sql += " and Blog_article_price<='" + maxPrice + "'";
            }
            if (minTime.Length != 0)
            {
                sql += " and Blog_article_Date>='" + minTime + "'";
            }
            if (maxTime.Length != 0)
            {
                sql += " and Blog_article_Date<='" + maxTime + "'";
            }
            if (catalog.Length != 0)
            {
                sql += " and Blog_catalog_id='" + catalog + "'";
            }
            if (title.Length != 0) {
                sql += " and Blog_article_title like'%" + title + "%'";
            }
            return Conn.DataValue(string.Format(sql, user_id));
        }//通过用户名获取文章数量转换为json

        public bool isMyArticle(string article_id, string user_id)
        {
            string sql = "select count(*) from blog_article where Blog_article_id='{0}' and Blog_user_id='{1}'";
            if (Conn.DataValue(string.Format(sql, article_id, user_id)) < 1)
            {
                return true;
            }
            return false;
        }//通过用户名和文章id判断是否属于

        public bool examine(string article_id)
        {
            string sql = "delete from blog_article  where Blog_article_id='{0}'";
            if (Conn.AddDelUp(string.Format(sql, article_id)) < 1)
            {
                return true;
            }
            return false;
        }//将文章删除

        public bool movedirectory(string article_id,string catalog){
            string sql = "update blog_article set Blog_catalog_id='{0}' where Blog_article_id='{1}'";
            if (Conn.AddDelUp(string.Format(sql, catalog, article_id))<1) {
                return true;
            }
            return false;
        }

        public bool upArticle(string title, string content, string time, string catalog, string price, string PayContent, string imgurl, string describe, string user_id)
        {
            string sql = "insert into blog_article(Blog_article_title,Blog_article_content,Blog_article_Date,Blog_catalog_id,Blog_article_price,Blog_article_charge,Blog_article_img,Blog_article_describe,Blog_user_id) " +
                "values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')";
            if (Conn.AddDelUp(string.Format(sql, title, content, time, catalog, price, PayContent, imgurl, describe, user_id)) < 1)
            {
                return true;
            }
            return false;
        }//插入文章到数据库

        public int wrting_EditDaosCount(string article_id, string user_id)
        {

            string sql = "select count(Blog_article_id) " +
                "from " +
                "blog_article as article " +
                "LEFT JOIN blog_user as users on article.Blog_user_id=users.Blog_user_id " +
                "where " +
                "article.Blog_article_id='{0}' and article.Blog_user_id='{1}'";
            return Conn.DataValue(string.Format(sql, article_id, user_id));
        }//判断文章是否数据指定用户名

        public List<Wrting_editDao> wrting_EditDaos(string article_id)
        {
            List<Wrting_editDao> list = new List<Wrting_editDao>();
            string sql = "Select " +
                "Blog_article_id," +
                "Blog_user_name," +
                "Blog_article_title," +
                "Blog_article_content," +
                "Blog_article_price," +
                "Blog_article_Date," +
                "Blog_article_img," +
                "Blog_catalog_id," +
                "Blog_article_describe," +
                "Blog_article_charge " +
                "from " +
                "blog_article as article " +
                "LEFT JOIN blog_user as users on article.Blog_user_id=users.Blog_user_id " +
                "where " +
                "article.Blog_article_id='{0}'";
            int i = 0;
            object[] arr = Conn.DataRowOne(string.Format(sql, article_id));
            Wrting_editDao wrting_EditDao = new Wrting_editDao(arr[0].ToString(), arr[1].ToString(), arr[2].ToString(), arr[3].ToString(), arr[4].ToString(), arr[5].ToString(), arr[6].ToString(), arr[7].ToString(), arr[8].ToString(), arr[9].ToString());
            list.Add(wrting_EditDao);
            return list;
        }

        public bool modifyArticle(string title, string content, string time, string catalog, string price, string PayContent, string imgurl, string describe,string article_id) {
            string sql = "update blog_article set Blog_article_title='{0}',Blog_article_content='{1}',Blog_article_Date='{2}',Blog_catalog_id='{3}',Blog_article_price='{4}',Blog_article_charge='{5}',Blog_article_img='{6}',Blog_article_describe='{7}' WHERE Blog_article_id='{8}'";
            if (Conn.AddDelUp(string.Format(sql, title, content, time, catalog, price, PayContent, imgurl, describe, article_id)) < 1)
            {
                return true;
            }
            return false;
        }
    }
}